package com.huazhi.wzsnw.util;

import com.huazhi.wzsnw.constant.CommonUser;
import com.huazhi.wzsnw.model.Department;
import com.huazhi.wzsnw.model.User;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.bson.Document;

import javax.servlet.http.HttpSession;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

public class ExportExcelUtil {
    private static MongoUtil mongoUtil = MongoUtil.instance;
    private XSSFCellStyle head_Style;
    private SXSSFWorkbook workbook;
    // 当前sheet
    private SXSSFSheet sheet;
    private SXSSFRow row = null;// 创建一行
    private SXSSFCell cell = null;
    private String headers[][];
    private int currentRow = 0;
    private XSSFCellStyle date_Style;
    private XSSFCellStyle time_Style;
    private XSSFCellStyle string_style;
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    private static SimpleDateFormat sdfForFileName = new SimpleDateFormat("yyyyMMddHHmmss");


    public static OutputStream excelProductList(OutputStream out, JSONObject result, String name, String [] head, HttpSession httpSession, List<String> headsName){
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
        Sheet sheet = wb.createSheet(name);// 创建一个sheet
        JSONArray jsonArray =result.getJSONArray("data");
        try {
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(16);// 设置行高
            Cell cell0=row0.createCell(0);
            cell0.setCellValue(name);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,head.length-1));
            CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
            Font font0 = wb.createFont();
            font0.setFontHeightInPoints((short) 12);// 字号
            font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead.setFont(font0);
            cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell0.setCellStyle(cellStyleHead);


            CellStyle cellStyleHead1 = wb.createCellStyle();
            Font font1= wb.createFont();
            font1.setFontHeightInPoints((short) 12);// 字号
            font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead1.setFont(font1);
            cellStyleHead1.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cellStyleHead1.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
            cellStyleHead1.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleHead1.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleHead1.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleHead1.setBorderLeft(CellStyle.BORDER_THIN);

            Row row1=sheet.createRow(1);
            for (int k=0;k<head.length;k++) {
                row1.setHeightInPoints(16);// 设置行高
                sheet.setColumnWidth(k, 60 * 100);
                Cell cell = row1.createCell(k);
                cell.setCellValue(head[k]);
                cell.setCellStyle(cellStyleHead1);
            }


            CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
            cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
            cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
            cellStyleBody.setWrapText(true);// 自动换行
            cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
            cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
            int beginRowNumber = 2;// 表体开始行
            if(jsonArray.size()!=0||jsonArray!=null){
                for (int i = 0; i < jsonArray.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //行高
                    rowDetail.setHeightInPoints(16);// 设置行高
                    for(int j=0;j<head.length;j++){
                        JSONObject jsonObject = JSONObject.fromObject(jsonArray.get(i));

                        for(int k=0;k<headsName.size();k++){
                            Cell cellbody0= rowDetail.createCell(j);
                            String value = jsonObject .getString(headsName.get(k));
                            cellbody0.setCellValue((String)(value));
                            cellbody0.setCellStyle(cellStyleBody);// 设置样式
                            j++;
                        }

                        break;
                    }
                }
            }

            Row lastRow = sheet.createRow(jsonArray.size() + beginRowNumber);
            lastRow.setHeightInPoints(16);// 设置行高
            Cell lastCell=lastRow.createCell(0);
            String datetime=(String)httpSession.getAttribute("datetime");
            User user=(User)httpSession.getAttribute("userInfo");
            lastCell.setCellValue("制表人："+user.getName()+"   制表时间："+datetime);
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(jsonArray.size()+2,jsonArray.size()+2,0,head.length-1));
            CellStyle cellStyleLast = wb.createCellStyle(); //标头样式
            Font fontLast = wb.createFont();
            fontLast.setFontHeightInPoints((short) 10);// 字号
            fontLast.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleLast.setFont(fontLast);
            cellStyleLast.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleLast.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            lastCell.setCellStyle(cellStyleLast);

            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
        return out;
    }
    //导出物资实时库存汇总表报表
    public static OutputStream timeCountExcel(OutputStream out, JSONObject result, String name, String [] head){
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
        Sheet sheet = wb.createSheet(name);// 创建一个sheet
        JSONArray jsonArray =result.getJSONArray("data");
        try {
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(16);// 设置行高
            Cell cell0=row0.createCell(0);
            cell0.setCellValue(name);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,head.length-1));
            CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
            Font font0 = wb.createFont();
            font0.setFontHeightInPoints((short) 12);// 字号
            font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead.setFont(font0);
            cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell0.setCellStyle(cellStyleHead);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(16);// 设置行高
            Cell cell1=row1.createCell(0);
            cell1.setCellValue("统计区间：截至目前为止所有数据");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(1,1,0,head.length-1));
            CellStyle cellStyleHead1 = wb.createCellStyle(); //标头样式
            Font font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 10);// 字号
            font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead1.setFont(font1);
            cellStyleHead1.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleHead1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell1.setCellStyle(cellStyleHead1);

            CellStyle cellStyleHead2 = wb.createCellStyle();
            Font font2= wb.createFont();
            font2.setFontHeightInPoints((short) 12);// 字号
            font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead2.setFont(font2);
            cellStyleHead2.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cellStyleHead2.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
            cellStyleHead2.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderLeft(CellStyle.BORDER_THIN);

            Row row2=sheet.createRow(2);
            for (int k=0;k<head.length;k++) {
                row2.setHeightInPoints(14);// 设置行高
                sheet.setColumnWidth(k, 60 * 100);
                Cell cell = row2.createCell(k);
                cell.setCellValue(head[k]);
                cell.setCellStyle(cellStyleHead2);
            }


            CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
            cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
            cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
            cellStyleBody.setWrapText(true);// 自动换行
            cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
            cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

            int beginRowNumber = 3;// 表体开始行
            if(jsonArray.size()!=0||jsonArray!=null){
                for (int i = 0; i < jsonArray.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //行高
                    rowDetail.setHeightInPoints(16);// 设置行高
                    for(int j=0;j<head.length;j++){
                        JSONObject jsonObject = JSONObject.fromObject(jsonArray.get(i));
                        Iterator<String> it = jsonObject.keys();
                        while(it.hasNext()){
                            Cell cellbody0= rowDetail.createCell(j);
                            String key = it.next();
                            String value = jsonObject.getString(key);
                            cellbody0.setCellValue((String)(value));
                            cellbody0.setCellStyle(cellStyleBody);// 设置样式
                            j++;
                        }
                        break;
                    }
                }
            }

            Row lastRow = sheet.createRow(jsonArray.size() + beginRowNumber);
            lastRow.setHeightInPoints(16);// 设置行高
            Cell lastCell=lastRow.createCell(0);
//            String datetime=(String)httpSession.getAttribute("datetime");
//            User user=(User)httpSession.getAttribute("userInfo");
            User user =CommonUser.getUser();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            lastCell.setCellValue("制表人："+user.getName()+"   制表时间："+simpleDateFormat.format(new Date()));
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(jsonArray.size()+3,jsonArray.size()+3,0,head.length-1));
            CellStyle cellStyleLast = wb.createCellStyle(); //标头样式
            Font fontLast = wb.createFont();
            fontLast.setFontHeightInPoints((short) 10);// 字号
            fontLast.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleLast.setFont(fontLast);
            cellStyleLast.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleLast.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            lastCell.setCellStyle(cellStyleLast);

            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
        return out;
    }

    //导出部门物资分布汇总表,物资异常数据汇总报表,物资分类汇总表
    public static OutputStream excel(OutputStream out, JSONObject result, String name, String [] head, HttpSession httpSession){
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
        Sheet sheet = wb.createSheet(name);// 创建一个sheet
        JSONArray jsonArray =result.getJSONArray("data");
        try {
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(16);// 设置行高
            Cell cell0=row0.createCell(0);
            cell0.setCellValue(name);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,head.length-1));
            CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
            Font font0 = wb.createFont();
            font0.setFontHeightInPoints((short) 12);// 字号
            font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead.setFont(font0);
            cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell0.setCellStyle(cellStyleHead);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(16);// 设置行高
            Cell cell1=row1.createCell(0);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year=sdf.format(new Date());
            cell1.setCellValue("统计区间："+year+"年01月-"+year+"年11月");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(1,1,0,head.length-1));
            CellStyle cellStyleHead1 = wb.createCellStyle(); //标头样式
            Font font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 10);// 字号
            font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead1.setFont(font1);
            cellStyleHead1.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleHead1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell1.setCellStyle(cellStyleHead1);

            CellStyle cellStyleHead2 = wb.createCellStyle();
            Font font2= wb.createFont();
            font2.setFontHeightInPoints((short) 12);// 字号
            font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead2.setFont(font2);
            cellStyleHead2.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cellStyleHead2.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
            cellStyleHead2.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderLeft(CellStyle.BORDER_THIN);

            Row row2=sheet.createRow(2);
            for (int k=0;k<head.length;k++) {
                row2.setHeightInPoints(14);// 设置行高
                sheet.setColumnWidth(k, 60 * 100);
                Cell cell = row2.createCell(k);
                cell.setCellValue(head[k]);
                cell.setCellStyle(cellStyleHead2);
            }

            CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
            cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
            cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
            cellStyleBody.setWrapText(true);// 自动换行
            cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
            cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

            int beginRowNumber = 3;// 表体开始行
            if(jsonArray.size()!=0||jsonArray!=null){
                for (int i = 0; i < jsonArray.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //行高
                    rowDetail.setHeightInPoints(16);// 设置行高
                    for(int j=0;j<head.length;j++){
                        JSONObject jsonObject = JSONObject.fromObject(jsonArray.get(i));
                        Iterator<String> it = jsonObject.keys();
                        while(it.hasNext()){
                            Cell cellbody0= rowDetail.createCell(j);
                            String key = it.next();
                            String value = jsonObject.getString(key);
                           cellbody0.setCellValue((String)(value));
                           cellbody0.setCellStyle(cellStyleBody);// 设置样式
                            j++;
                        }
                        break;
                    }
                }
            }

            Row lastRow = sheet.createRow(jsonArray.size()+ beginRowNumber);
            lastRow.setHeightInPoints(16);// 设置行高
            Cell lastCell=lastRow.createCell(0);
//            String datetime=(String)httpSession.getAttribute("datetime");
//            User user=(User)httpSession.getAttribute("userInfo");
//            lastCell.setCellValue("制表人："+user.getUsername()+"   制表时间："+datetime);
            User user =CommonUser.getUser();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            lastCell.setCellValue("制表人："+user.getName()+"   制表时间："+simpleDateFormat.format(new Date()));
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(jsonArray.size()+3,jsonArray.size()+3,0,head.length-1));
            CellStyle cellStyleLast = wb.createCellStyle(); //标头样式
            Font fontLast = wb.createFont();
            fontLast.setFontHeightInPoints((short) 10);// 字号
            fontLast.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleLast.setFont(fontLast);
            cellStyleLast.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleLast.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            lastCell.setCellStyle(cellStyleLast);

            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
        return out;
    }

    public static OutputStream  dimensionalSummaryExcel(OutputStream out, JSONObject result, String name, List<Department> dataResult, HttpSession httpSession){
        try{
            JSONArray jsonArray =result.getJSONArray("data");
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
            Sheet sheet = wb.createSheet(name);// 创建一个sheet
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(16);// 设置行高
            Cell cell0=row0.createCell(0);
            cell0.setCellValue(name);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,dataResult.size()*5+3-1));
            CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
            Font font0 = wb.createFont();
            font0.setFontHeightInPoints((short) 12);// 字号
            font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead.setFont(font0);
            cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell0.setCellStyle(cellStyleHead);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(16);// 设置行高
            Cell cell1=row1.createCell(0);
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            String year=sdf.format(new Date());
            cell1.setCellValue("统计区间："+year+"年01月-"+year+"年11月");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(1,1,0,dataResult.size()*5+3-1));
            CellStyle cellStyleHead1 = wb.createCellStyle(); //标头样式
            Font font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 10);// 字号
            font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead1.setFont(font1);
            cellStyleHead1.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleHead1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell1.setCellStyle(cellStyleHead1);


            CellStyle cellStyleHead2 = wb.createCellStyle();
            Font font2= wb.createFont();
            font2.setFontHeightInPoints((short) 12);// 字号
            font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead2.setFont(font2);
            cellStyleHead2.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cellStyleHead2.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
            cellStyleHead2.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderLeft(CellStyle.BORDER_THIN);



            //第三行
          //  String[] row_second = {"序号", "类别名称", "软件部", "","","","","信息部","","","","","类别总数合计"};
            List<String> row_second=new ArrayList<String>();
            row_second.add("序号");
            row_second.add("类别名称");

            for(Department department:dataResult){
                row_second.add(department.getDepartmentname());
                row_second.add("");
                row_second.add("");
                row_second.add("");
                row_second.add("");
            }
            row_second.add("类别总数合计");


            List<String> row_secondname =new  ArrayList<String>();
            row_secondname.add("number");
            row_secondname.add("category");

            for(int i=0;i<dataResult.size();i++){
                row_secondname.add("one"+i);
                row_secondname.add("two"+i);
                row_secondname.add("three"+i);
                row_secondname.add("four"+i);
                row_secondname.add("five"+i);
            }
            row_secondname.add("count");



            Row r2 = sheet.createRow(2);
            for (int i = 0; i < row_second.size(); i++) {
                Cell tempCell = r2.createCell(i);
                sheet.setColumnWidth(i, 60 * 100);
                tempCell.setCellValue(row_second.get(i));
                tempCell.setCellStyle(cellStyleHead2);
                if(i==0){

                }else if(i==1){

                }else {
                    if(row_second.size()==(i+1)){

                    }else {
                        if (!"".equals(row_second.get(i))) {
                            sheet.addMergedRegion(new CellRangeAddress(2, 2, i, i + 4));
                        }
                    }
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
            sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));

//            for(int i=0;i<dataResult.size();i++){
//                sheet.addMergedRegion(new CellRangeAddress(2, 2, dataResult.size()+5*i, i*5+6));
//            }


//            sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 6));
//            sheet.addMergedRegion(new CellRangeAddress(2, 2, 7, 11));
//            sheet.addMergedRegion(new CellRangeAddress(2, 2, 12, 16));



            sheet.addMergedRegion(new CellRangeAddress(2, 3, dataResult.size()*5+3-1, dataResult.size()*5+3-1));

            //第四行
           // String[] row_third = {"","", "物资总数", "在库数量", "出库数量", "出库数量", "报废数量", "物资总数", "在库数量", "出库数量", "出库数量", "报废数量", ""};
            List<String> row_third=new ArrayList<String>();
            row_third.add("");
            row_third.add("");
            for(Department department:dataResult){
                row_third.add("物资总数");
                row_third.add("在库数量");
                row_third.add("出库数量");
                row_third.add("维保数量");
                row_third.add("报废数量");
            }
            row_third.add("");

            Row r3 = sheet.createRow(3);
            for (int i = 0; i < row_third.size(); i++) {
                Cell tempCell = r3.createCell(i);
                sheet.setColumnWidth(i, 60 * 100);
                tempCell.setCellValue(row_third.get(i));
                tempCell.setCellStyle(cellStyleHead2);
            }

            CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
            cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
            cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
            cellStyleBody.setWrapText(true);// 自动换行
            cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
            cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

            int beginRowNumber = 4;// 表体开始行
            if(jsonArray.size()!=0||jsonArray!=null){
                for (int i = 0; i < jsonArray.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //行高
                    rowDetail.setHeightInPoints(16);// 设置行高
                    for(int j=0;j<row_second.size();j++){
                        JSONObject jsonObject = JSONObject.fromObject(jsonArray.get(i));

                        for(int k=0;k<row_secondname.size();k++){
                            Cell cellbody0= rowDetail.createCell(j);
                            String value = jsonObject .getString(row_secondname.get(k));
                            cellbody0.setCellValue((String)(value));
                            cellbody0.setCellStyle(cellStyleBody);// 设置样式
                            j++;
                        }
                        break;
                    }
                }
            }

            Row lastRow = sheet.createRow(jsonArray.size() + beginRowNumber);
            lastRow.setHeightInPoints(16);// 设置行高
            Cell lastCell=lastRow.createCell(0);
            User user =CommonUser.getUser();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            lastCell.setCellValue("制表人："+user.getName()+"   制表时间："+simpleDateFormat.format(new Date()));
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(jsonArray.size()+beginRowNumber,jsonArray.size()+beginRowNumber,0,dataResult.size()*5+3-1));
            CellStyle cellStyleLast = wb.createCellStyle(); //标头样式
            Font fontLast = wb.createFont();
            fontLast.setFontHeightInPoints((short) 10);// 字号
            fontLast.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleLast.setFont(fontLast);
            cellStyleLast.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleLast.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            lastCell.setCellStyle(cellStyleLast);

            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
        return out;
    }



    public static OutputStream flowMeterExcle(OutputStream out, List<Document> list, String name, String [] head){
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
        Sheet sheet = wb.createSheet(name);// 创建一个sheet
        Row row0=sheet.createRow(0);
        row0.setHeightInPoints(16);// 设置行高
        Cell cell0=row0.createCell(0);
        cell0.setCellValue(name);
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,head.length-1));
        CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
        Font font0 = wb.createFont();
        font0.setFontHeightInPoints((short) 12);// 字号
        font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
        cellStyleHead.setFont(font0);
        cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
        cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
        cell0.setCellStyle(cellStyleHead);


        CellStyle cellStylehead = wb.createCellStyle(); //标头样式
        Font font1 = wb.createFont();
        font1.setFontHeightInPoints((short) 12);// 字号
        font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
        cellStylehead.setFont(font1);
        cellStylehead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
        cellStylehead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
        cellStylehead.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
        cellStylehead.setBorderTop(CellStyle.BORDER_THIN);
        cellStylehead.setBorderRight(CellStyle.BORDER_THIN);
        cellStylehead.setBorderBottom(CellStyle.BORDER_THIN);
        cellStylehead.setBorderLeft(CellStyle.BORDER_THIN);

        CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
        cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
        cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
        cellStyleBody.setWrapText(true);// 自动换行
        cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
        cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
        cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
        cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
        cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
        cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
        try {
            Row row1 = sheet.createRow(1);
            for (int k=0;k<head.length;k++) {
                sheet.setColumnWidth(k, 60 * 100);
                Cell cell = row1.createCell(k);
                cell.setCellValue(head[k]);
                cell.setCellStyle(cellStylehead);
            }
            int beginRowNumber = 2;// 表体开始行
            if(list.size()!=0||list!=null){
                for (int i = 0; i < list.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //行高
                    rowDetail.setHeightInPoints(16);// 设置行高
                    for(int j=0;j<head.length;j++){
                        Cell cell= rowDetail.createCell(j);
                        cell.setCellValue((String)(list.get(i).get("epcCode")));
                        cell.setCellStyle(cellStyleBody);// 设置样式

                        Cell cell1= rowDetail.createCell(j+1);
                        cell1.setCellValue((String)(list.get(i).get("productName")));
                        cell1.setCellStyle(cellStyleBody);// 设置样式

                        Cell cell2= rowDetail.createCell(j+2);
                        cell2.setCellValue((String)(list.get(i).get("type")));
                        cell2.setCellStyle(cellStyleBody);// 设置样式

                        Cell cell3= rowDetail.createCell(j+3);
                        cell3.setCellValue((String)(list.get(i).get("createTime")));
                        cell3.setCellStyle(cellStyleBody);// 设置样式

                        Cell cell4= rowDetail.createCell(j+4);
                        cell4.setCellValue((Integer)(list.get(i).get("number")));
                        cell4.setCellStyle(cellStyleBody);// 设置样式
                        break;
                    }
                }
            }
            Row rowDetail = sheet.createRow(list.size() + beginRowNumber);
            rowDetail.setHeightInPoints(14);// 设置行高
            Cell cell= rowDetail.createCell(0);
            cell.setCellValue("合并");
            cell.setCellStyle(cellStyleBody);// 设置样式

            Cell cell1= rowDetail.createCell(1);
            cell1.setCellValue("");
            cell1.setCellStyle(cellStyleBody);// 设置样式

            Cell cell2= rowDetail.createCell(2);
            cell2.setCellValue("");
            cell2.setCellStyle(cellStyleBody);// 设置样式

            Cell cell3= rowDetail.createCell(3);
            cell3.setCellValue("");
            cell3.setCellStyle(cellStyleBody);// 设置样式

            Cell cell4= rowDetail.createCell(4);
            cell4.setCellValue(list.size());
            cell4.setCellStyle(cellStyleBody);// 设置样式

            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
        return out;
    }

    //导出物资实时库存汇总表报表
    public static OutputStream reportingLedgerinExcel(OutputStream out, JSONObject result, String name, String [] head, HttpSession httpSession,List<String> headsName){
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);// 创建excel文档,在工作簿构建时指定窗口大小
        Sheet sheet = wb.createSheet(name);// 创建一个sheet
        JSONArray jsonArray =result.getJSONArray("data");
        try {
            Row row0=sheet.createRow(0);
            row0.setHeightInPoints(16);// 设置行高
            Cell cell0=row0.createCell(0);
            cell0.setCellValue(name);
            sheet.addMergedRegion(new CellRangeAddress(0,0,0,head.length-1));
            CellStyle cellStyleHead = wb.createCellStyle(); //标头样式
            Font font0 = wb.createFont();
            font0.setFontHeightInPoints((short) 12);// 字号
            font0.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead.setFont(font0);
            cellStyleHead.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell0.setCellStyle(cellStyleHead);

            Row row1=sheet.createRow(1);
            row1.setHeightInPoints(16);// 设置行高
            Cell cell1=row1.createCell(0);
            cell1.setCellValue("统计区间：截至目前为止所有数据");
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(1,1,0,head.length-1));
            CellStyle cellStyleHead1 = wb.createCellStyle(); //标头样式
            Font font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 10);// 字号
            font1.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead1.setFont(font1);
            cellStyleHead1.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleHead1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cell1.setCellStyle(cellStyleHead1);

            CellStyle cellStyleHead2 = wb.createCellStyle();
            Font font2= wb.createFont();
            font2.setFontHeightInPoints((short) 12);// 字号
            font2.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleHead2.setFont(font2);
            cellStyleHead2.setAlignment(CellStyle.ALIGN_CENTER);// 水平对齐
            cellStyleHead2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            cellStyleHead2.setFillBackgroundColor(CellStyle.ALIGN_CENTER);
            cellStyleHead2.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleHead2.setBorderLeft(CellStyle.BORDER_THIN);

            Row row2=sheet.createRow(2);
            for (int k=0;k<head.length;k++) {
                row2.setHeightInPoints(14);// 设置行高
                sheet.setColumnWidth(k, 60 * 100);
                Cell cell = row2.createCell(k);
                cell.setCellValue(head[k]);
                cell.setCellStyle(cellStyleHead2);
            }


            CellStyle cellStyleBody = wb.createCellStyle();// 表体单元格样式
            cellStyleBody.setAlignment(CellStyle.ALIGN_LEFT);// 水平对齐
            cellStyleBody.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直对齐
            cellStyleBody.setWrapText(true);// 自动换行
            cellStyleBody.setBorderTop(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderRight(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyleBody.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyleBody.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
            cellStyleBody.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平

            int beginRowNumber = 3;// 表体开始行
            if(jsonArray.size()!=0||jsonArray!=null){
                for (int i = 0; i < jsonArray.size(); i++) {
                    Row rowDetail = sheet.createRow(i + beginRowNumber);  //行高
                    rowDetail.setHeightInPoints(16);// 设置行高
                    for(int j=0;j<head.length;j++){
                        JSONObject jsonObject = JSONObject.fromObject(jsonArray.get(i));
                        for(int k=0;k<headsName.size();k++){
                            Cell cellbody0= rowDetail.createCell(j);
                            String value = jsonObject .getString(headsName.get(k));
                            cellbody0.setCellValue((String)(value));
                            cellbody0.setCellStyle(cellStyleBody);// 设置样式
                            j++;
                        }
                        break;
                    }
                }
            }




            Row lastRow = sheet.createRow(jsonArray.size() + beginRowNumber);
            lastRow.setHeightInPoints(16);// 设置行高
            Cell lastCell=lastRow.createCell(0);
            User user =CommonUser.getUser();
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            lastCell.setCellValue("制表人："+user.getUsername()+"   制表时间："+simpleDateFormat.format(new Date()));
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(jsonArray.size()+3,jsonArray.size()+3,0,head.length-1));
            CellStyle cellStyleLast = wb.createCellStyle(); //标头样式
            Font fontLast = wb.createFont();
            fontLast.setFontHeightInPoints((short) 10);// 字号
            fontLast.setBoldweight(Font.BOLDWEIGHT_NORMAL);// 加粗
            cellStyleLast.setFont(fontLast);
            cellStyleLast.setAlignment(CellStyle.ALIGN_RIGHT);// 水平对齐
            cellStyleLast.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 上下居中
            lastCell.setCellStyle(cellStyleLast);

            wb.write(out);
        }catch (Exception e){
            e.printStackTrace();
        }
        return out;
    }







}
